package com.roc.service.impl;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.roc.bean.BlackListBean;
import com.roc.dao.DaoException;
import com.roc.dao.IBaseDao;
import com.roc.dao.ISQLBaseDao;
import com.roc.dao.black.IBlackDao;
import com.roc.pojo.BlackList;
import com.roc.pojo.MessageSend;
import com.roc.pojo.Operators;
import com.roc.service.ServiceException;
import com.roc.service.black.IBlackService;
import com.roc.util.Constance;
import com.roc.util.DateUtil;
import com.roc.util.Utils;
import com.roc.util.constant.SysLogType;
@Service
@Transactional
public class BlackServiceImpl implements IBlackService{
	static Logger logger=Logger.getLogger(BlackServiceImpl.class);

	@Autowired
	private IBlackDao blackDao;
	@Autowired
	private IBaseDao baseDao;
	@Autowired
	private ISQLBaseDao sqlBaseDao;
	@Override
	public void addBlack(BlackList black,Operators oper) throws ServiceException {
		// TODO Auto-generated method stub
		try{
			black.setOperators(oper.getAccountNo());
			black.setIsused("Y");
			black.setUnlockTimes(0L);
			black.setCol1(0L);
			black.setCol2(0L);
			Date date=new Date();
			black.setDtCreate(Constance.formateDate(date, Constance.DATA_FORMAT_PATTERN_01));
			black.setDtLastmod(Constance.formateDate(date, Constance.DATA_FORMAT_PATTERN_01));
			
			blackDao.editSave(black);
			String typename="";
			if(black.getAddType().equals("H1")){
				typename="调单拒付";
			}else if(black.getAddType().equals("H2")){
				typename="非法经营";
			}else if(black.getAddType().equals("H3")){
				typename="套现嫌疑";
			}
			//给风控发短信。
			MessageSend messSend = new MessageSend();
			messSend.setPhoneNumber(Utils.getConfigValue("telephone"));
			messSend.setMesContent("银行卡【"+black.getCardNo()+"】进行了以下操作【"+typename+"】被打入了黑名单");
			messSend.setSendFlag("0");
			messSend.setDtCreate(date);
			messSend.setDtLastmod(date);
			blackDao.editSave(messSend);
			blackDao.addSyslog(SysLogType.BLACK_ADD.getCode(), SysLogType.BLACK_ADD.getName(), oper.getAccountNo(), "操作员【"+oper.getAccountNo()+"】录入黑名单【"+black.getMid()+"】");
		}catch(DaoException e){
			logger.debug(e.getMessage(), e);
			throw new ServiceException("插入黑名单信息失败");
		}
	}
	@Override
	public void changeStatus(BlackList black, String isused,Operators oper)
			throws ServiceException {
		// TODO Auto-generated method stub
		try{
			black.setIsused(isused);
			if(isused.equals("N")){
				Long times=black.getUnlockTimes()+1;
				black.setUnlockTimes(times);
			}
			black.setDtLastmod(Constance.formateDate(new Date(), Constance.DATA_FORMAT_PATTERN_01));
			blackDao.editUpdate(black);
			
			if(isused.equals("N")){
				blackDao.addSyslog(SysLogType.BLACK_STATUS.getCode(), SysLogType.BLACK_STATUS.getName(), oper.getAccountNo(), "操作员【"+oper.getAccountNo()+"】更改商户【"+black.getMid()+"】为启用状态");
			}else{
				blackDao.addSyslog(SysLogType.BLACK_STATUS.getCode(), SysLogType.BLACK_STATUS.getName(), oper.getAccountNo(), "操作员【"+oper.getAccountNo()+"】更改商户【"+black.getMid()+"】为不启用状态");
			}
			
		}catch(DaoException e){
			logger.debug(e.getMessage(), e);
			throw new ServiceException("更改黑名单状态失败");
		}
	}
	@Override
	public BlackList queryByNo(String acctNo, String cardNo, String licenseNo)
			throws ServiceException {
		// TODO Auto-generated method stub
		BlackList black=null;
		try{
			black=blackDao.queryByNo(acctNo, cardNo, licenseNo);
			
		}catch(DaoException e){
			logger.debug(e.getMessage(),e);
			throw new ServiceException("查询唯一黑名单失败");
		}
		return black;
	}
	@Override
	public int addBlackList(BlackListBean black,Operators op) throws ServiceException {
		String sql="INSERT INTO APSTFR.TBL_POS_BLACK_INF(BLACK_NUM,BLACK_TYPE,IN_DATA,IN_TIME,IN_REASON,IN_FLAG,REMARK1) VALUES(?,?,?,?,?,?,?)";
		int effect=0;
		List<Object> parmas=new ArrayList<Object>();
		parmas.add(black.getBlackNum());
		parmas.add(black.getBlackType());
		parmas.add(black.getDate());
		parmas.add(black.getTime());
		parmas.add(black.getReason());
		parmas.add("2");
		parmas.add(op.getId());
		try {
			effect=sqlBaseDao.deleteBySql(sql, parmas);
			if(effect>0)
			{
				logger.info("添加【"+black.getBlackNum()+"】到黑名单成功!");
				baseDao.addSyslog(SysLogType.BLACK_ADD.getCode(), SysLogType.BLACK_ADD.getName(), op.getAccountNo(), op.getAccountNo()+"添加【"+black.getBlackNum()+"】到黑名单");
			}
		} catch (DaoException e) {
			logger.debug(e.getMessage(),e);
			throw new ServiceException("添加黑名单失败");
		}
		return effect;
	}
	@Override
	public BlackListBean findByBlackNum(String blackNum)
			throws ServiceException {
		String sql="SELECT S.BLACK_NUM,S.BLACK_TYPE,S.IN_DATA,S.IN_TIME,S.IN_REASON,S.IN_FLAG FROM APSTFR.TBL_POS_BLACK_INF S WHERE S.BLACK_NUM=?";
		Object[] parmas={blackNum};
		BlackListBean black=null;
		try {
			Object object=sqlBaseDao.findObj(sql, parmas);
			if(object!=null)
			{
				Object[] obj=(Object[]) object;
				black=new BlackListBean();
				black.setBlackNum(Constance.Null2EmptyTrim(obj[0]));
				black.setBlackType(Constance.Null2EmptyTrim(obj[1]));
				black.setDate(Constance.Null2EmptyTrim(obj[2]));
				black.setTime(Constance.Null2EmptyTrim(obj[3]));				
				black.setReason(Constance.Null2EmptyTrim(obj[4]));
				black.setStatus(Constance.Null2EmptyTrim(obj[5]));
			}
		} catch (DaoException e) {
			logger.debug(e.getMessage(),e);
			throw new ServiceException("查询【"+blackNum+"】失败");
		}
		return black;
	}
	@Override
	public int updateBlackList(BlackListBean black,Operators op) throws ServiceException {
		String sql="UPDATE APSTFR.TBL_POS_BLACK_INF SET BLACK_TYPE=?,IN_DATA=?,IN_TIME=?,IN_REASON=?,IN_FLAG=? WHERE BLACK_NUM=?";
		int effect=0;
		List<Object> parmas=new ArrayList<Object>();
		parmas.add(black.getBlackType());
		parmas.add(black.getDate());
		parmas.add(black.getTime());	
		parmas.add(black.getReason());
		parmas.add(black.getStatus());
		parmas.add(black.getBlackNum());
		try {
			effect=sqlBaseDao.deleteBySql(sql, parmas);
			if(effect>0)
			{
				logger.info("更改黑名单【"+black.getBlackNum()+"】成功!");		
				baseDao.addSyslog(SysLogType.BLACK_STATUS.getCode(), SysLogType.BLACK_STATUS.getName(), op.getAccountNo(), op.getAccountNo()+"更改黑名单【"+black.getBlackNum()+"】成功");
			}
		} catch (DaoException e) {
			logger.debug(e.getMessage(),e);
			throw new ServiceException("更改黑名单失败");
		}
		return effect;
	}

	@Override
	public Map<String,Object> insertExportBlackList(File file,Operators op) throws ServiceException {
		Map<Integer,List<Object>> uploadData=new HashMap<Integer, List<Object>>();//存放上传数据
		Map<String,Object> resultMap=new HashMap<String, Object>();//上传文件错误原因及定位
		int count=0;
		try{		
		//上传Excel文件流转换为Map集合
		logger.info("上传Excel文件流转换为Map集合");
		boolean uploadResult=transfromToExcel(file, uploadData,resultMap);
		if(!uploadResult)
		{
			return resultMap;//返回错误原因
		}
		//验证Excel表数据正确性
		logger.info("验证Excel表数据正确性");
		boolean correntValidate=verifyDataCorrectness(uploadData, resultMap);
		if(!correntValidate)
		{
			return resultMap;//返回校验原因
		}
		//导入数据库
		logger.info("开始进行批量导入操作");
		SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMddHHmmss");
		String date=sdf.format(new Date());
		Iterator<Entry<Integer, List<Object>>> it=uploadData.entrySet().iterator();
		//检测是否重复数据
		while(it.hasNext())
		{
			Entry<Integer, List<Object>> entry=it.next();
			List<Object> list=entry.getValue();
			//循环列					
			String blackNum=Constance.Null2EmptyTrim(list.get(0));			
			BlackListBean temp=findByBlackNum(blackNum);
			if(temp!=null)
			{
				resultMap.put("result","导入失败,商户号或卡号【"+blackNum+"】已存在!!!");
				logger.info("停止批量导入,已存在数据【"+blackNum+"】");
				return resultMap;
			}
		}
		//开始导入数据
		it=uploadData.entrySet().iterator();
		while(it.hasNext())
		{
			Entry<Integer, List<Object>> entry=it.next();
			List<Object> list=entry.getValue();
			//循环列					
			BlackListBean black=new BlackListBean();
			black.setBlackNum(Constance.Null2EmptyTrim(list.get(0)));
			black.setBlackType(Constance.Null2EmptyTrim(list.get(1)));
			black.setDate(date.substring(0, 8));
			black.setTime(date.substring(8));
			black.setReason(Constance.Null2EmptyTrim(list.get(2)));
			black.setStatus(Constance.Null2EmptyTrim(list.get(3)));			
			addBlackList(black, op);
			count++;
		}
		}catch(Exception e){
			logger.debug(e.getMessage(), e);
			throw new ServiceException("批量导入黑名单失败");
		}
		resultMap.put("result","导入成功!总计导入"+count+"条数据");
		return resultMap;
	}
	/**
	 * 验证数据正确性
	 * @param map
	 */
	public boolean verifyDataCorrectness(Map<Integer,List<Object>> map,Map<String,Object> resultMap)
	{
		Iterator<Entry<Integer, List<Object>>> it=map.entrySet().iterator();
		String error_reason=null;
		while(it.hasNext())
		{
			Entry<Integer, List<Object>> entry=it.next();
			List<Object> list=entry.getValue();			
			//商户号或卡号验证
			String regex="^[a-zA-Z0-9']{1,30}$";
			String num=Constance.Null2EmptyTrim(list.get(0));
			if(!Pattern.matches(regex,num))
			{
				error_reason="第"+(entry.getKey()+1)+"行第1列商户号或卡号不规范,只能包含数字字符且长度不能超过30，中间也不能有空格并且不能为空";
				logger.info(error_reason);
				resultMap.put("result", error_reason);
				return false;
			}
			/*String num=Constance.Null2EmptyTrim(list.get(0));
			if("".equals(num)||num==null)
			{
				error_reason="第"+(entry.getKey()+1)+"行第1列商户号或卡号不规范,也不能为空";
				logger.info(error_reason);
				resultMap.put("result", error_reason);
				return false;
			}*/
			
			String cardType=Constance.Null2EmptyTrim(list.get(1));
			if(!cardType.equals("C")&&!cardType.equals("M")&&!cardType.equals("D")&&!cardType.equals("N")&&!cardType.equals("P")&&!cardType.equals("O"))
			{
				error_reason="第"+(entry.getKey()+1)+"行第2列卡类型值不规范,只能为C,M,D,N,P,O中的一个;";
				logger.info(error_reason);
				resultMap.put("result", error_reason);
				return false;
			}
			String reason=Constance.Null2EmptyTrim(list.get(2));
			if(reason.length()>100||"".equals(reason))
			{
				error_reason="第"+(entry.getKey()+1)+"行第3列原因值不规范,长度不能超过100且不能为空";
				logger.info(error_reason);
				resultMap.put("result", error_reason);
				return false;
			}
			String flag=Constance.Null2EmptyTrim(list.get(3));
			if(!flag.equals("2"))
			{
				error_reason="第"+(entry.getKey()+1)+"行第4列卡状态值不规范,只能为2";
				logger.info(error_reason);
				resultMap.put("result", error_reason);
				return false;
			}
		}
		return true;
	}
	/**
	 * 将File装换成Map集合
	 * @param file
	 * @param map
	 */
	@SuppressWarnings("deprecation")
	protected boolean transfromToExcel(File file,Map<Integer,List<Object>> map,Map<String,Object> resultMap)
	{
		try {
			// 构造 XSSFWorkbook 对象，strPath 传入文件路径
			XSSFWorkbook xwb = new XSSFWorkbook(file.getPath());
			// 得到Excel工作表对象
			XSSFSheet sheet = xwb.getSheetAt(0);
			// 得到行数
			int trLength = sheet.getPhysicalNumberOfRows();
			if(trLength == 0){
				logger.info("上传文件缺少列名");
				resultMap.put("result", "上传文件缺少列名");
				return false;
			}			
			logger.info("Excel中记录的总行数是：" + (trLength - 1));			
			// 每条记录列数
			Row row = sheet.getRow(0);
						
			int tdLength = row.getPhysicalNumberOfCells();
			if(tdLength<4){
				logger.info("上传的文件缺少必需的列名");
				resultMap.put("result", "上传的文件缺少必需的列名");
				return false;
			}
			if(tdLength>4){
				logger.info("上传的文件存在多余的列名");
				resultMap.put("result", "上传的文件存在多余的列名");
				return false;
			}
			logger.info("每条记录的列数："+tdLength);
				
			//验证标题是否一致
			String reason_error=null;
			String[] title={"卡号或商户号","类型","原因","状态"};
			String titleName=null;
			for(int i=0;i<4;i++)
			{
				row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
				titleName=row.getCell(i).getStringCellValue();
				if(!titleName.equals(title[i]))
				{
					reason_error="第1行第"+(i+1)+"列的列名不正确,正确的列名为【"+title[i]+"】";
					logger.info(reason_error);
					resultMap.put("result", reason_error);
					return false;
				}
			}
			if(trLength == 1){
				logger.info("上传文件内容为空");
				resultMap.put("result", "上传文件内容为空");
				return false;
			}
			for (int i = 1; i < trLength; i++) {
				List<Object> tdList = new ArrayList<Object>();
				Row r = sheet.getRow(i);
				
				for (int j = 0; j < tdLength; j++) {
					Cell c = r.getCell(j);
					/*
					 * 将所有列的内容都设置成String类型，否则会出Excel异常： Cannot get a text value
					 * from a numeric cell
					 */
					if (c != null) {
						c.setCellType(Cell.CELL_TYPE_STRING);
						tdList.add(c.getStringCellValue());
					}else{
						tdList.add("");
					}					
				}
				map.put(i, tdList);
			}
		} catch (Exception e) {
			logger.error("将Excel中数据封装到List容器中异常：" + e.getMessage());
			resultMap.put("result", "将Excel中数据封装到List容器中异常");
			return false;
		}
		return true;
	}
	@Override
	public int delBlackListByBlackNum(String blackNum) throws ServiceException {
		String sql="DELETE FROM APSTFR.TBL_POS_BLACK_INF WHERE  BLACK_NUM=?";
		int effect=0;
		List<Object> parmas=new ArrayList<Object>();
		parmas.add(blackNum);				
		try {
			effect=sqlBaseDao.deleteBySql(sql, parmas);
			if(effect>0)
			{
				logger.info("删除黑名单【"+blackNum+"】成功!");		
			}
		} catch (DaoException e) {
			logger.debug(e.getMessage(),e);
			throw new ServiceException("更改黑名单失败");
		}
		return effect;
	}
	/**
	 * 黑名单审核不通过
	 */
	@Override
	public int blackAuditNoPassService(String blackNum, Operators op) throws ServiceException {
		// TODO Auto-generated method stub
		String sql="update  APSTFR.TBL_POS_BLACK_INF t set t.in_flag=? ,t.remark2=? WHERE  BLACK_NUM=?  ";
		int effect=0;
		List<Object> parmas=new ArrayList<Object>();
		parmas.add("0");
		parmas.add(op.getId());
		parmas.add(blackNum);				
		try {
			effect=sqlBaseDao.deleteBySql(sql, parmas);
			if(effect>0)
			{
				logger.info("黑名单审核【"+blackNum+"】成功!");		
			}
		} catch (DaoException e) {
			logger.debug(e.getMessage(),e);
			throw new ServiceException("黑名单审核失败");
		}
		return effect;
	}
	/**
	 * 黑名单审核通过
	 */
	@Override
	public int blackAuditPassService(String blackNum, Operators op) throws ServiceException {
		// TODO Auto-generated method stub
		String sql="update  APSTFR.TBL_POS_BLACK_INF t set t.in_flag=? ,t.remark2=? WHERE  BLACK_NUM=?  ";
		int effect=0;
		List<Object> parmas=new ArrayList<Object>();
		parmas.add("1");
		parmas.add(op.getId());
		parmas.add(blackNum);				
		try {
			effect=sqlBaseDao.deleteBySql(sql, parmas);
			if(effect>0)
			{
				logger.info("黑名单审核【"+blackNum+"】成功!");		
			}
		} catch (DaoException e) {
			logger.debug(e.getMessage(),e);
			throw new ServiceException("黑名单审核失败");
		}
		return effect;
	}
	
}
